/*
 * AccessLogDao.java
 *
 * Created on 2007-11-22, PM 2:51
 *
 * Permission to use, copy, and distribute this software for
 * NON-COMMERCIAL purposes and without fee is hereby granted
 * provided that this copyright notice appears in all copies.
 */

package orest.common.dao;

import java.sql.*;
import orest.common.valueobject.ColumnAccessLogVal;
import orest.common.util.DataBase;
import orest.common.util.IDGenerator;
import orest.common.response.*;
import orest.common.util.Tools;
import java.util.ArrayList;

/**
 *
 * @author yan dongdong
 */
public class ColumnAccessLogDao {
    
    /** Creates a new instance of AccessLogDao */
    public ColumnAccessLogDao () {
    }
    
    public boolean addUserAccessRecord(ColumnAccessLogVal obj) {
        boolean result = true;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        Tools tool = new Tools();
        
        try{
        	String id = IDGenerator.getIDGenerator("T_ColumnAccessLog").getID(2);
        	
                String sql = "insert into  T_ColumnAccessLog(";
                sql += " ColumnAccessLogID, AccessTime, ColumnID, ColumnName,ColumnInfoID,";
                sql += " ColumnInfoName, UserType,UserID,IP";
                sql += " ) values('";
                sql += id + "', '";               
                sql += tool.gettimestamp() + "', '";
                sql += obj.getColumnID () + "', '";
                sql += obj.getColumnName () + "', '";
                sql += obj.getColumnInfoID () + "', '";
                sql += obj.getColumnInfoName () + "', '";
                sql += obj.getUserType () + "', '";
                sql += obj.getUserID () + "', '";
           
                sql += obj.getIP () + "')";
                System.out.println (sql);
                conn = db.getSysConnectionPool();
                pstmt = conn.prepareStatement (sql);
                pstmt.execute ();
                return result;
        }
        catch(SQLException e){
            e.printStackTrace();
            result = false;
            return result;
        }
        catch(Exception e){
        	e.printStackTrace();
            result = false;
            return result;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                	e.printStackTrace();
                }
            }
            
            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                	e.printStackTrace();
                }
            }
        }
    }
   
    public boolean findColumnAccessInfobyCondition(Response rp,String condtion,
                                                                int pageNo,
                                                                int pageRecord,
                                                                int total) {

        boolean result = true;
      //  boolean firsthalf = true;
        ArrayList al = new ArrayList();
        int iPrePageCount ;
        int tempRow = 0;
        Tools tool = new Tools();

        /* number of record in page */
        int startRow = (pageNo -1) * pageRecord;
        int endRow = pageNo * pageRecord;

        if(endRow > total)
            endRow = total;




        tempRow = 1;

        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String rowcount = "select top " + Integer.toString(endRow);
        String sql = "  * from  T_ColumnAccessLog where  " + condtion;



            sql += " order by AccessTime desc";
        /* sql string end */

        /*
           count plan production then encapsulat as the entity bean
           and save them into ArrayList
         */
        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (rowcount + sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                if(tempRow > startRow) {
                 ColumnAccessLogVal accesslogVal = new ColumnAccessLogVal();
                 accesslogVal.setColumnAccessLogID(rs.getString("ColumnAccessLogID"));
                 accesslogVal.setAccessTime(rs.getDate("AccessTime"));
                 accesslogVal.setColumnID(rs.getString("ColumnID"));
                 accesslogVal.setColumnName(rs.getString("ColumnName"));
                 accesslogVal.setColumnInfoID(rs.getString("ColumnInfoID"));
                 accesslogVal.setColumnInfoName(rs.getString("ColumnInfoName"));
                 accesslogVal.setUserType(rs.getString("UserType"));
                 accesslogVal.setUserID(rs.getString("UserID"));           
                 accesslogVal.setIP(rs.getString("IP"));
                 al.add (accesslogVal);
                }

                else
                    tempRow++;
            }


                rp.setValObjs (al);

            return true;
        }
        catch(SQLException e){
            System.out.println("When get the  log info, have a exception!" +
                                " The class name is ColumnAccessLogDao." + e);
            return false;
        }
        catch(Exception e){
            System.out.println ("When call the getMyConnection, have a"+
                                 "exception occured!   ");
            System.out.println ("The class is ColumnAccessLogDao and the " +
                                 "function is findColumnAccessInfobyCondition! " + e);

            return false;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                 //   pstmt = conn.prepareStatement ("set rowcount 0");
                 //   pstmt.execute ();
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ColumnAccessLogDao, when" +
                            " close the stmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ColumnAccessLogDao, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of ColumnAccessLogDao and" +
                            " the function is findColumnAccessInfobyCondition, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }
 
   

     public long countColumnAccessInfobyCondition(String condtion, Response rp) {

        ArrayList al = new ArrayList();
        long total = 0;

        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String sql = "select count(1) as cnt from  T_ColumnAccessLog where  " + condtion;
        /* sql string end */

        /*
           count  production then encapsulat as the entity bean
           and save them into ArrayList
         */
        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                total = rs.getLong("cnt");
            }
            return total;
        }
        catch(SQLException e){
            System.out.println("When countColumnAccessInfobyCondition, have a exception!" +
                                " The class name is ColumnAccessLogDao." + e);
            return 0;
        }
        catch(Exception e){
            System.out.println ("When call the countColumnAccessInfobyCondition, have a"+
                                 "exception occured!   ");
            e.printStackTrace();

            return 0;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ColumnAccessLogDao, when" +
                            " close the stmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ColumnAccessLogDao, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of ColumnAccessLogDao and" +
                            " the function is countColumnAccessInfobyCondition, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }

     public boolean countColumnAccessTotalbyCondition(String condtion, Response rp) {

        ArrayList al = new ArrayList();
        long total = 0;
         boolean result = true;
        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String sql = "select ColumnID,ColumnName,UserType,count(*) as cnt from  T_ColumnAccessLog  where  " + condtion + " group by ColumnID,ColumnName,UserType" ;
        /* sql string end */

        /*
           count  production then encapsulat as the entity bean
           and save them into ArrayList
         */
        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                  al.add (new ColumnAccessLogVal(
                                     rs.getString ("ColumnID"),
                                     rs.getString("ColumnName"),
                                     rs.getString("UserType"),
                                     rs.getLong("cnt")
                                    ));
               // total = rs.getLong("cnt");
            }
            rp.setValObjs (al);
            return result;
        }
        catch(SQLException e){
            e.printStackTrace();
            result = false;
            return result;
        }
        catch(Exception e){
            e.printStackTrace();
            result = false;
            return result;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ColumnAccessLogDao, when" +
                            " close the stmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ColumnAccessLogDao, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of ColumnAccessLogDao and" +
                            " the function is countColumnAccessTotalbyCondition, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }

     public boolean countColumnAccessTotalbyCondition2(String condtion, Response rp) {

        ArrayList al = new ArrayList();
        long total = 0;
         boolean result = true;
        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String sql = "select ColumnID,ColumnName,count(*) as cnt from  T_ColumnAccessLog  where  " + condtion + " group by ColumnID,ColumnName order by ColumnID" ;
        /* sql string end */

        /*
           count  production then encapsulat as the entity bean
           and save them into ArrayList
         */
        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                  al.add (new ColumnAccessLogVal(
                                     rs.getString ("ColumnID"),
                                     rs.getString("ColumnName"),
                                     rs.getLong("cnt")
                                    ));
               // total = rs.getLong("cnt");
            }
            rp.setValObjs (al);
            return result;
        }
        catch(SQLException e){
            e.printStackTrace();
            result = false;
            return result;
        }
        catch(Exception e){
            e.printStackTrace();
            result = false;
            return result;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ColumnAccessLogDao, when" +
                            " close the stmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ColumnAccessLogDao, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of ColumnAccessLogDao and" +
                            " the function is countColumnAccessTotalbyCondition2, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }
    
}
